﻿package pta10;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
	
public class PreparedStatementTest {
	private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private static String driverName = "com.mysql.jdbc.Driver";
	private static String userName = "root";//root
	private static String password = "201521123032";
	private static ResultSet rs;
	private static PreparedStatement pst;
	private static Statement st;
	private static Connection conn=null;
	private static int resultNum = 0;
	
	public static Student getStudentByName(String name) throws SQLException{
		//select * from student where name='小明'
		//select * from students where name like '周%';
		conn = DriverManager
				.getConnection(url,userName,password);
		String sql="select * from student where name=?";
		pst=conn.prepareStatement(sql);
		pst.setString(1, name);
		rs=pst.executeQuery();	
		Student student=null;
		while(rs.next()){
			student=new Student(rs.getString("stuno"),rs.getString("name"),rs.getInt("age"),rs.getDate("birthdate"));
		}
		return student;
	
	//new	StatementTest.realeaseAll(rs, pst, conn);
	} 
	//查找所有年龄小于age的所有学生并放入一个列表返回。
	public static List<Student> getStudentsByAgeBelow(int age) throws SQLException  {
		List<Student> stulist=new ArrayList<Student>();
		conn = DriverManager
				.getConnection(url,userName,password);
		String sql="select * from student where age<?";
		pst=conn.prepareStatement(sql);
		pst.setInt(1, age);
		rs=pst.executeQuery();
		while(rs.next()){
			//Student student=new S
			stulist.add(new Student(rs.getString("stuno"),rs.getString("name"),
					rs.getInt("age"), rs.getDate("birthdate")));
		}
		return stulist;
	}
	//显示出生年月日在某个范围内的所有学生
	public static void displayStudentBetween(String begin,String end) throws SQLException{
		//如使用between '2010-01-02' and '2011-02-23',改写成 between ? and ?
		//select * from student where birthdate between '2010-01-02' and '2011-02-23'
		conn=DriverManager.getConnection(url,userName,password);
		String sql="select * from student where birthdate between ? and ?";
		pst=conn.prepareStatement(sql);
		pst.setDate(1, java.sql.Date.valueOf(begin));
		pst.setDate(2, java.sql.Date.valueOf(end));
		rs=pst.executeQuery();
		while(rs.next()){
			System.out.println(rs.getDate("birthdate"));
		}
	}
	//显示所有年龄超过传入参数age的同学的姓名与年龄平均值(使用avg函数)
	public static double getAvgAbove(int age) throws SQLException{
		//select name,avg(age) avgAge from student where age>? 
		//注意：avg(age) avgAge指的是将年龄求平均后作为avgAge列输出，即列名为avgAge
		conn=DriverManager.getConnection(url,userName,password);
		String sql="select name,avg(age) avgAge from student where age>?";
		pst=conn.prepareStatement(sql);
		pst.setInt(1, age);
		rs=pst.executeQuery();
		double avg = 0;
		while(rs.next()){
			System.out.println(rs.getString("name"));
			avg=rs.getDouble("avgAge");
		}
		return avg;
	}
	public static void main(String[] args) throws SQLException{
		//System.out.println(getAvgAbove(18));
		//displayStudentBetween("1990-01-01", "2000-01-01");
		//	System.out.println(getStudentByName("huangdengfeng"));
		//System.out.println(getStudentsByAgeBelow(20));
		StatementTest.realeaseAll(rs, pst, conn);
	}
}
